using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using OneSoft.EntityCore;
using OneSoft.DataAccess;
using BySoft.Server.Common.Entity;
using BySoft.Server.Common.Schema;

namespace BySoft.Server.DataAccess
{
    public partial class MdMaterialDA
    {


        private static string GetHeadSQL()
        {
            return @"SELECT A.*,b.MaterialTypeName,sm.MaterialSmallTypeName
                    FROM MD_Material A left outer join MD_MaterialType b on a.MaterialTypeID=b.MaterialTypeID
                        left outer join dbo.MD_MaterialSmallType sm on a.MaterialSmallTypeID=sm.MaterialSmallTypeID
                    WHERE 1=1";
        }
        public static DataTable SelectList(string MaterialID, string MaterialName, string MatTypeID, string Status, int pageIndex, int pageSize, out int m_count)
        {
            string strcmd = GetHeadSQL();
                
//@"SELECT ma.*,m.MaterialTypeName from Md_Material as ma 
//                            left join Md_MaterialType as m on m.MaterialTypeID=ma.MaterialTypeID
//                            where 1=1";

            DataAccessParameterCollection dpc = new DataAccessParameterCollection();
            if (!string.IsNullOrEmpty(MaterialID))
            {
                strcmd += " and a.MaterialID LIKE @MaterialID";
                dpc.AddWithValue("@MaterialID", "%" + MaterialID + "%");
            }
            if (!string.IsNullOrEmpty(MaterialName))
            {
                strcmd += " and a.MaterialName LIKE @MaterialName";
                dpc.AddWithValue("@MaterialName", "%" + MaterialName + "%");
            }
            if (!string.IsNullOrEmpty(MatTypeID))
            {
                strcmd += " and a.MaterialTypeID = @MatTypeID";
                dpc.AddWithValue("@MatTypeID", MatTypeID);
            }
            if (!string.IsNullOrEmpty(Status))
            {
                strcmd += " and a.Status = @Status";
                dpc.AddWithValue("@Status", Status);
            }

            strcmd += " ORDER BY a.MaterialID DESC,a.ModifyDate DESC";

            return DataAccessCommon.ExecuteDataTablePaging(strcmd, pageIndex, pageSize, dpc, out m_count);
        }

        public static DataTable SelectList(string strMaterialTypeName, int pageIndex, int pageSize, out int count)
        {
            try
            {
                #region SQL Server
                string strcmd = GetHeadSQL();

                DataAccessParameterCollection dpc = new DataAccessParameterCollection();



                if (!string.IsNullOrEmpty(strMaterialTypeName))
                {
                    strcmd += " and A.MaterialTypeName LIKE @MaterialTypeName";
                    dpc.AddWithValue("@MaterialTypeName", "%" + strMaterialTypeName + "%");
                }


                strcmd += " ORDER BY A.MaterialTypeID DESC";

                return DataAccessCommon.ExecuteDataTablePaging(strcmd, pageIndex, pageSize, dpc, out count);
                #endregion
            }
            catch
            {
                throw;
            }
        }
        public static bool IsExistsNameSpecifications(string strMaterialName, string strSpecifications)
        {

            #region SQL Server
            string strcmd = GetHeadSQL();

            DataAccessParameterCollection dpc = new DataAccessParameterCollection();



            if (!string.IsNullOrEmpty(strMaterialName))
            {
                strcmd += " and A.MaterialTypeName =@MaterialTypeName";
                dpc.AddWithValue("@MaterialTypeName", strMaterialName);
            }

            if (!string.IsNullOrEmpty(strSpecifications))
            {
                strcmd += " and A.Specifications =@Specifications";
                dpc.AddWithValue("@Specifications", strSpecifications);
            }

            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    object obj = broker.ExecuteSQLScalar(strcmd, dpc);
                    if (Convert.ToInt32(obj.ToString().Trim()) > 0)
                    {
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }
                catch
                {
                    throw;
                }
            }

            #endregion

        }



        public static bool SaveMdMaterialWithAttachment(MdMaterialEntity entity, List<MdAttachmentEntity> lstyFile,bool isNew)
        {
            bool isResult = true;
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    if (isNew)
                    {
                        DataAccessCommon.Insert(entity, broker);
                        foreach (MdAttachmentEntity Detailentity in lstyFile)
                        {
                            Detailentity.AttachmentID = Guid.NewGuid().ToString();
                            DataAccessCommon.Insert(Detailentity, broker);
                        }
                    }
                    else
                    {
                        //update attachment
                        DataAccessCommon.Update(entity, broker);
                        ClsCommDA.DelFile("Material", entity.MaterialID, broker);
                        foreach (MdAttachmentEntity Detailentity in lstyFile)
                        {
                            Detailentity.AttachmentID = Guid.NewGuid().ToString();
                            DataAccessCommon.Insert(Detailentity, broker);
                        }


                    }
                  
                    broker.Commit();
                }
                catch (Exception ex)
                {
                    isResult = false;
                    broker.Rollback();
                    throw ex;
                }

            }
            return isResult;
        }

       
    }
}

